﻿using Business.Base;
using Business.Common;
using Configuration;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Business.CentrifugalPump.Perf
{

    public class CentrifugalPumpPerfExperimentDBManager : IDBManager
    {
        
        private String fields = "experimentCode,  designFlow, designHead,    designShaftPower, designPumpEfficiency,   designNPSH, dInlet,  dOutlet,  designStages,  designRatedSpeed,  designSynchronousSpeed, designRatedVoltage,    designRatedCurrent,  designWindingConnection, designWindingMaterial, currentTransformationRatio, motorEfficiency, zInlet, zOutlet, lInlet, lOutlet ,kInlet,kOutlet,flowmeter,flowmeterAddress,experimentResultFlow,experimentResultHead,experimentResultPower,experimentResultEff,experimentResultFlowIsPass,experimentResultHeadIsPass,experimentResultPowerIsPass,experimentResultEffIsPass";
        private String values = "@experimentCode, @designFlow, @designHead, @designShaftPower, @designPumpEfficiency, @designNPSH, @dInlet, @dOutlet, @designStages, @designRatedSpeed, @designSynchronousSpeed, @designRatedVoltage, @designRatedCurrent, @designWindingConnection, @designWindingMaterial,@currentTransformationRatio,@motorEfficiency,@zInlet,@zOutlet,@lInlet,@lOutlet,@kInlet,@kOutlet,@flowmeter,@flowmeterAddress,@experimentResultFlow,@experimentResultHead,@experimentResultPower,@experimentResultEff,@experimentResultFlowIsPass,@experimentResultHeadIsPass,@experimentResultPowerIsPass,@experimentResultEffIsPass";
        

        private String table =  "perfExperiment";
        SqlConnection connection = null;

        private SqlCommand      sqlSelectCountCommand = null;
        private SqlCommand      sqlSelectCommand = null;
        private SqlDataAdapter  sqlSelectAdapter = null;
        private SqlCommand      sqlInsertCommand = null;

        private SqlCommand      sqlDeleteCommand = null;

        public CentrifugalPumpPerfExperimentDBManager()
        {
            String connectionString = DataSourceConfig.DataSourceString; 
            connection = new SqlConnection(connectionString);

            sqlSelectCountCommand = new SqlCommand();
            sqlSelectCountCommand.Connection = connection;

            sqlSelectCommand = new SqlCommand();
            sqlSelectCommand.Connection = connection;

            sqlInsertCommand = new SqlCommand();
            sqlInsertCommand.Connection = this.connection;
            sqlInsertCommand.CommandText = "INSERT INTO [" + table + "] ("+ fields + ") VALUES ("+ values + ")";

            sqlDeleteCommand = new SqlCommand();
            sqlDeleteCommand.Connection = this.connection;
            sqlDeleteCommand.CommandText = "delete from " + table;

            sqlSelectAdapter = new SqlDataAdapter();
            sqlSelectAdapter.SelectCommand = sqlSelectCommand;
        }



        public int GetCount(BaseQuery query)
        {
            throw new NotImplementedException();
        }

        public DataTable GetDataList(BaseQuery query)
        {
            throw new NotImplementedException();
        }

        public int Insert(object obj)
        {
            CentrifugalPumpPerfExperiment perfExperiment = (CentrifugalPumpPerfExperiment)obj;
            sqlInsertCommand.Parameters.Clear();
            sqlInsertCommand.Parameters.Add(new SqlParameter("@experimentCode", perfExperiment.Code));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@designFlow", perfExperiment.DesignFlow));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@designHead", perfExperiment.DesignHead));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@designShaftPower", perfExperiment.DesignShaftPower));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@designPumpEfficiency", perfExperiment.DesignPumpEfficiency));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@designNPSH", perfExperiment.DesignNPSH));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@designStages", perfExperiment.DesignStages));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@designRatedSpeed", perfExperiment.DesignRatedSpeed));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@designSynchronousSpeed", perfExperiment.DesignSynchronousSpeed));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@designRatedVoltage", perfExperiment.DesignRatedVoltage));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@designRatedCurrent", perfExperiment.DesignRatedCurrent));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@designWindingConnection", perfExperiment.DesignWindingConnection));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@designWindingMaterial", perfExperiment.DesignWindingMaterial) );          

            sqlInsertCommand.Parameters.Add(new SqlParameter("@currentTransformationRatio", perfExperiment.CurrentTransformationRatio));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@motorEfficiency", perfExperiment.MotorEfficiency));

            sqlInsertCommand.Parameters.Add(new SqlParameter("@dInlet", perfExperiment.DInlet));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@dOutlet", perfExperiment.DOutlet));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@zInlet", perfExperiment.ZInlet));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@zOutlet", perfExperiment.ZOutlet));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@lInlet", perfExperiment.LInlet));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@lOutlet", perfExperiment.LOutlet));

            sqlInsertCommand.Parameters.Add(new SqlParameter("@kInlet", perfExperiment.KInlet));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@kOutlet", perfExperiment.KOutlet));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@flowmeter", perfExperiment.Flowmeter));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@flowmeterAddress", perfExperiment.FlowmeterAddress));

            sqlInsertCommand.Parameters.Add(new SqlParameter("@experimentResultFlow", perfExperiment.ExperimentResultFlow));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@experimentResultHead", perfExperiment.ExperimentResultHead));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@experimentResultPower", perfExperiment.ExperimentResultPower));

            sqlInsertCommand.Parameters.Add(new SqlParameter("@experimentResultEff", perfExperiment.ExperimentResultEff));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@experimentResultFlowIsPass", perfExperiment.ExperimentResultFlowIsPass));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@experimentResultHeadIsPass", perfExperiment.ExperimentResultHeadIsPass));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@experimentResultPowerIsPass", perfExperiment.ExperimentResultPowerIsPass));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@experimentResultEffIsPass", perfExperiment.ExperimentResultEffIsPass));



            connection.Open();
            int id = (int)sqlInsertCommand.ExecuteScalar();
            connection.Close();
            return id;
        }

        public void Delete(Object id)
        {
            if (id != null)
            {
                this.sqlDeleteCommand.CommandText = "delete from " + table + " where experimentCode = @code";
                sqlDeleteCommand.Parameters.Add(new SqlParameter("@code", (string)id));
            }            
            connection.Open();
            sqlDeleteCommand.ExecuteNonQuery();
            sqlDeleteCommand.Parameters.Clear();
            connection.Close();
        }

   
        public void DeleteByCondtion(string condition)
        {
            this.sqlDeleteCommand.CommandText = "delete from " + table + condition;
            connection.Open();
            sqlDeleteCommand.ExecuteNonQuery();
            sqlDeleteCommand.Parameters.Clear();
            connection.Close();
        }
    }




}
